package com.gzcsqic.excelapp.utils;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    /**
     * 插入图片
     * @param workbook
     * @param sheet
     * @param picPath
     * @param startCol
     * @param startRow
     * @throws IOException
     */
    public static void setPic(XSSFWorkbook workbook, XSSFSheet sheet, String picPath, int startCol, int startRow) throws IOException {
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        File image = new File(picPath);
        BufferedImage bufferImg = ImageIO.read(image);
        ImageIO.write(bufferImg, "png", byteArrayOut);
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();

        // 获取单元格默认宽高
        XSSFRow row = sheet.getRow(1);
        Cell cell = row.createCell(0);
        double cellWidth = Math.ceil(sheet.getColumnWidthInPixels(cell.getColumnIndex()));
        double cellHeight = Math.ceil(cell.getRow().getHeightInPoints() / 72 * 96);

        int maxCol = 5,maxRow=16;
        // excel模板中图片区域的最大宽高
        double maxWidth = maxCol * cellWidth;
        double maxHeight = maxRow * cellHeight;
        // 图片文件的宽高
        BufferedImage sourceImg = ImageIO.read(image);
        double imageWidth = sourceImg.getWidth();
        double imageHeight = sourceImg.getHeight();

        double imgRate = imageWidth / imageHeight;
        if(imgRate >=1){ //横图
            int col2 = startCol + maxCol;
            double imgHeight = Math.ceil(maxWidth / (imageWidth / imageHeight));
            int rowCount = (int) Math.floor(imgHeight / cellHeight);
            startRow = startRow + (int)Math.ceil((maxRow-rowCount) / 2); // 设置起始y，垂直居中
            int row2 = startRow + rowCount;
            int dy2 = (int) (imgHeight -rowCount*cellHeight)+5;
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, XSSFShape.EMU_PER_PIXEL *dy2, (short) startCol, startRow, (short)col2, row2);
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));

        }else{ // 竖图
            double imgWidth = Math.ceil((imageWidth / imageHeight) * maxHeight);
            long colCount = (long) Math.floor(imgWidth / cellWidth);
            long row2 = startRow + maxRow;
            startCol = startCol + (int)Math.ceil((maxCol-colCount) / 2);
            int col2 = (int) (startCol + colCount);
            int dx2 = (int) (imgWidth - colCount * cellWidth);
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, XSSFShape.EMU_PER_PIXEL *dx2, 0, (short) startCol, startRow, (short)col2, (int)row2);
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
        }
    }


    public static List<Map<String,String>> readData(String filePath,String sheetName) throws IOException {
        List<Map<String,String>> list = new ArrayList<>();
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(filePath)));
        XSSFSheet sheet = workbook.getSheet(sheetName);
        for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
            Map<String,String> map = new LinkedHashMap<>();
            XSSFRow row = sheet.getRow(j);
            map.put("id",j+"");
            for (int k = 0; k < row.getLastCellNum(); k++) {
                map.put("c" + k,getCellVal(row.getCell(k)));
            }
            list.add(map);
        }
        return list;
    }

    // 格式化读取excel单元格数据
    public static String getCellVal(Cell cell){
        HSSFDataFormatter formatter = new HSSFDataFormatter();
        String s = formatter.formatCellValue(cell);
        return s;
    }

    public static void cloneSheet(File excelFile, String srcSheetName, String destSheetName) {
        Workbook sheets = readExcelFromFile(excelFile);
        int index = sheets.getSheetIndex(srcSheetName);
        cloneSheet(excelFile, index, destSheetName);
    }

    public static void cloneSheet(File excelFile, Integer index, String destSheetName) {
        Workbook sheets = readExcelFromFile(excelFile);
        //克隆一个新的sheet
        Sheet newSheet = sheets.cloneSheet(index);
        int sheetIndex = sheets.getSheetIndex(newSheet);
        sheets.setSheetName(sheetIndex, destSheetName);
        try {
            FileOutputStream out = new FileOutputStream(excelFile);
            out.flush();
            sheets.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //读取excel
    public static Workbook readExcelFromFile(File file) {
        if (file == null) {
            return null;
        }
        try {
            return new XSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            throw new RuntimeException("文件解析失败");
        }
    }

    public static void saveExcel(Workbook wb,File outFile) {
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(outFile);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
